﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ScrewTurn.Wiki;
using UniBasel.Biozentrum.NMR;
using System.Text.RegularExpressions;
using ScrewTurn.Wiki.PluginFramework;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Configuration;

public partial class MySamples : BasePage
{
	#region Page Validators

	/// <summary>
	/// Custom Validator. args.IsValid when no <script> tags are present. 
	/// </summary>
	/// <param name="source"></param>
	/// <param name="args"></param>
	protected void ValidateForScripts(object source, ServerValidateEventArgs args)
	{
		args.IsValid = !Regex.IsMatch(args.Value.ToString(), @"\<script.*?\>");
	}

	/// <summary>
	/// The name of Sample must be unique. this function validates agains the database if the name is already in use.
	/// </summary>
	/// <param name="source"></param>
	/// <param name="args"></param>
	protected void UniqueNameValidate(object source, ServerValidateEventArgs args)
	{
		#region Open Connection and Create Command
		SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NMRUserDataConnectionString"].ConnectionString);
		MyConnection.Open();

		SqlCommand checkSpectrometerNameSELECTQuery = MyConnection.CreateCommand();
		#endregion

		checkSpectrometerNameSELECTQuery.CommandText = @"
SELECT
	name
FROM
	nmruserdata.dbo.samples s,
	nmruserdata.dbo.relation_samples_to_users rsu
WHERE
	s.id=rsu.sampleid AND
	rsu.username='##username##' AND
	s.name='##samplename##'
".Replace("##username##", SessionFacade.Username).Replace("##samplename##", tbName.Text); ;

		SqlDataReader myReader = checkSpectrometerNameSELECTQuery.ExecuteReader();

		if (myReader.HasRows) args.IsValid = false;
		else args.IsValid = true;

		myReader.Close();
		MyConnection.Close();
	}
	#endregion

	#region (Samples Table) SampleToolsMultiView View0 Elements
	protected void SamplesTableGv_RowCommand(object sender, GridViewCommandEventArgs e)
	{
		string currentCommand = e.CommandName;

		if (currentCommand != "showSelectedSampleInfo") return;

		SampleTools.ActiveViewIndex = 2;

		int currentRowIndex = Int32.Parse(e.CommandArgument.ToString());

		string SampleID = SamplesTableGridView.DataKeys[currentRowIndex].Value.ToString();

		showSampleInfowithID(SampleID);
	}

	protected void AddSampleButton_Click(object sender, EventArgs e)
	{
		SampleTools.ActiveViewIndex = 1;
		UniqueNamecv.Enabled = true;
		tbName.ReadOnly = false;
		butSubmit.CommandName = "Add";
		UpdateInfopn.Style.Add("display", "none");
		tbContactPerson.Text = SessionFacade.Username;

		Users u = Users.Instance;
		UserInfo user = u.Find(SessionFacade.Username);

		tbContactEmail.Text = user.Email;

		lblTitle.Text = "Add New Sample";

		NMRGenUtils.checkUserCredentials(SessionFacade.Username);
	}
	#endregion

	#region (Add New Sample Dialog) SampleToolsMultiView View1 Elements

	/// <summary>
	/// Clears the content of form
	/// </summary>
	/// <param name="sender"></param>
	/// <param name="e"></param>
	protected void butClear_Click(object sender, EventArgs e)
	{
		tbName.Text = "";
		tbMemo.Text = "";
		tbContactEmail.Text = "";
		tbContactPerson.Text = "";
		tbBuffer.Text = "";
		tbLabeling.Text = "";
		tbContent.Text = "";
		tbVersionInfo.Text = "";
	}

	protected void butSubmit_Command(object sender, CommandEventArgs e)
	{
		if (!Page.IsValid) return;

		#region Open DB Connection and Create Command
		SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NMRUserDataConnectionString"].ConnectionString);
		MyConnection.Open();

		SqlCommand NewSampleINSERTQuery = MyConnection.CreateCommand();
		#endregion

		string CommandText; //will depend on whether new sample added or an existing is being updated
		string LogMsg;

		CommandText = @"
INSERT INTO samples
                         (name, buffer, labeling, memo, contact, contactmail, [content], version, versioninfo)
VALUES        (N'##name##', N'##buffer##', N'##labeling##',N'##memo##', N'##contact##', N'##contactmail##',N'##content##',N'##version##',N'##versioninfo##')
";

		switch (e.CommandName.ToString())
		{
		    case "Add":
				CommandText = CommandText.Replace("##version##", "1").Replace("##versioninfo##", "Original");
				LogMsg = "New Sample added.";
				break;
			case "Update":
				CommandText = CommandText.Replace("##version##", (getLastVersion(tbName.Text) + 1).ToString());
				CommandText = CommandText.Replace("##versioninfo##", tbVersionInfo.Text);
				LogMsg = "Sample with ID " + e.CommandArgument.ToString() + " updated.";
				tbName.ReadOnly = false; //It became true when update button was clicked.
				break;
			default:
				throw (new ArgumentException("Only Add or Update values are possible."));
		}
		CommandText = CommandText.Replace("##name##", tbName.Text).Replace("##memo##", tbMemo.Text).
								 Replace("##content##", tbContent.Text.Replace(@"'", @"\'")).Replace("##contact##", tbContactPerson.Text).
								 Replace("##contactmail##", tbContactEmail.Text).Replace("##buffer##", tbBuffer.Text).
								 Replace("##labeling##", tbLabeling.Text);
		NewSampleINSERTQuery.CommandText = CommandText;

		Debug.WriteLine("NewSampleINSERTQuery " + CommandText);
		SqlDataReader myReader = NewSampleINSERTQuery.ExecuteReader();

		myReader.Close();

		SqlCommand getLastInsertedID = MyConnection.CreateCommand();
		getLastInsertedID.CommandText = @"SELECT @@IDENTITY";

		long SamplID = Convert.ToInt32(getLastInsertedID.ExecuteScalar());

		Log.LogEntry(LogMsg, EntryType.General, SessionFacade.Username);

		string un = NMRGenUtils.checkUserCredentials(SessionFacade.Username);
		NewSampleINSERTQuery.CommandText = @"
INSERT INTO relation_samples_to_users
VALUES (N'##username##', ##sampleid##);
".Replace("##username##", un).Replace("##sampleid##", SamplID.ToString());
		myReader = NewSampleINSERTQuery.ExecuteReader();

		myReader.Close();

		MyConnection.Close();
		SampleTools.ActiveViewIndex = 0;
		butClear_Click(sender, e);
		SamplesTableGridView.DataBind();
	}

	#endregion

	#region (Show Sample) SampleToolsMultiView View2 Elements

	protected void VersionLinkButton_Click(object sender, CommandEventArgs e)
	{
		string currentCommand = e.CommandName;
		if (currentCommand != "ShowSampleInfoWithID") return;

		SampleTools.ActiveViewIndex = 2;

		string SampleID = e.CommandArgument.ToString();

		showSampleInfowithID(SampleID);
	}

	protected void btnDelete2_Click(object sender, EventArgs e)
	{
		btnDelete2.Enabled = false;
		btnCancelDelete2.Visible = true;
		btnRealyDelete2.Visible = true;
		lblDeleteQuestion.Visible = true;
	}
	protected void btnCancelDelete2_Click(object sender, EventArgs e)
	{
		btnDelete2.Enabled = true;
		btnRealyDelete2.Visible = false;
		btnCancelDelete2.Visible = false;
		lblDeleteQuestion.Visible = false;
	}
	protected void btnUpdate2_Command(object sender, CommandEventArgs e)
	{
		SampleTools.ActiveViewIndex = 1;
		UniqueNamecv.Enabled = false;

		butSubmit.CommandName = "Update";
		UpdateInfopn.Style.Add("display", "inline");

		string SampleID = e.CommandArgument.ToString();

		#region Open DB Connection and Create Command
		SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NMRUserDataConnectionString"].ConnectionString);
		MyConnection.Open();

		SqlCommand getSampleInfoQuery = MyConnection.CreateCommand();
		#endregion

		getSampleInfoQuery.CommandText = @"
SELECT
	name AS Name,
	[date] AS [Date],
	memo AS [Short Description],
	contact AS Contact,
	contactmail AS [Email],
	content AS Content,
	[version] AS [Version],
	buffer AS Buffer,
	labeling AS Labeling
FROM
	nmruserdata.dbo.samples s,
	nmruserdata.dbo.relation_samples_to_users rsu
WHERE
	id=##id## AND
	rsu.username=N'##username##' AND
	rsu.sampleid=s.id
".Replace("##id##", SampleID).Replace("##username##", SessionFacade.Username);

        SqlDataReader myReader = getSampleInfoQuery.ExecuteReader();

        if (!myReader.HasRows)
		{
			Log.LogEntry("Show Sample with ID=" + SampleID + ". SqlQuery returned no rows.", EntryType.Error, SessionFacade.Username);
			throw (new ArgumentOutOfRangeException("SampleID", "There is no Sample with ID=" + SampleID + "."));
		}
		myReader.Read();

		lblTitle.Text = "Change of Sample " + myReader["Name"];
		tbName.Text = myReader["Name"].ToString();
		tbMemo.Text = myReader["Short Description"].ToString();
		tbBuffer.Text = myReader["Buffer"].ToString();
		tbLabeling.Text = myReader["Labeling"].ToString();
		tbContactPerson.Text = myReader["Contact"].ToString();
		tbContactEmail.Text = myReader["Email"].ToString();
		tbContent.Text = myReader["Content"].ToString();


		tbName.ReadOnly = true;
		myReader.Close();
		MyConnection.Close();

	}

	protected void btnRealyDelete2_Command(object sender, CommandEventArgs e)
	{
		string SampleID = e.CommandArgument.ToString();

		SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NMRUserDataConnectionString"].ConnectionString);
		MyConnection.Open();

		SqlCommand deleteSampleInfoQuery = MyConnection.CreateCommand();
		deleteSampleInfoQuery.CommandText = "DELETE FROM relation_samples_to_users WHERE sampleid=##sampleid##".Replace("##sampleid##", SampleID);
		SqlDataReader MyReader = deleteSampleInfoQuery.ExecuteReader();
		MyReader.Close();

		deleteSampleInfoQuery.CommandText = "DELETE FROM samples WHERE id=##sampleid##".Replace("##sampleid##", SampleID);
		MyReader = deleteSampleInfoQuery.ExecuteReader();
		MyReader.Close();

		MyConnection.Close();
		btnCancelDelete2_Click(sender, e);

		SampleTools.ActiveViewIndex = 0;
		SamplesTableGridView.DataBind();
	}
	#endregion

	protected void showSampleInfowithID(string SampleID)
	{
		
		#region OpenConnection and Create Command
		SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NMRUserDataConnectionString"].ConnectionString);
		MyConnection.Open();

		SqlCommand getSampleInfoSELECTQuery = MyConnection.CreateCommand();
		#endregion

		getSampleInfoSELECTQuery.CommandText = @"
SELECT
	name AS Name,
	[date] AS [Date],
	memo AS [Short Description],
	contact AS Contact,
	contactmail AS [Email],
	content AS Content,
	[version] AS [Version],
	buffer AS Buffer,
	labeling AS Labeling
FROM
	nmruserdata.dbo.samples AS s1,
	nmruserdata.dbo.relation_samples_to_users AS rsu
WHERE
	rsu.sampleid=s1.id AND
	rsu.username='##username##' AND
	rsu.sampleid=##id##
".Replace("##id##", SampleID).Replace("##username##", SessionFacade.Username);

		SqlDataReader myReader = getSampleInfoSELECTQuery.ExecuteReader();

		if (!myReader.HasRows)
		{
			Log.LogEntry("Show Sample with ID=" + SampleID + ". SQLQuery returned no rows.", EntryType.Error, SessionFacade.Username);
			throw (new ArgumentOutOfRangeException("SampleID", "There is no Sample with ID=" + SampleID + "."));
		}


		myReader.Read();
		lblTitle.Text = "<b>" + myReader["Name"].ToString() + "</b> sample.";
		lblMemo2.Text = myReader["Short Description"].ToString();
		lblDate2.Text = "Version <b>" + myReader["Version"].ToString() + "</b> from " + myReader["Date"].ToString() + ".";
		lblBuffer2.Text = "Buffer: " + myReader["Buffer"].ToString();
		tbContent2.Text = myReader["Content"].ToString();
		lblContact2.Text = @"Responsible person: <a href=""mailto://" + myReader["Email"].ToString() + @""">" + myReader["Contact"].ToString() + "</a>.";
		lblLabeling2.Text = "Labeling: " + myReader["Labeling"].ToString();
		btnUpdate2.CommandArgument = SampleID;
		btnRealyDelete2.CommandArgument = SampleID;
		butSubmit.CommandArgument = myReader["Version"].ToString();

		myReader.Close();
		MyConnection.Close();

		PrevVersionsSQLDatasource.SelectParameters["id"].DefaultValue = SampleID.ToString();
		PrevVersionsSQLDatasource.DataBind();
	}

	private uint getLastVersion(string SpecName)
	{
		uint result;
		SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NMRUserDataConnectionString"].ConnectionString);
		MyConnection.Open();

		SqlCommand getLastVersionSELECTQuery = MyConnection.CreateCommand();
		getLastVersionSELECTQuery.CommandText = @"
SELECT
	MAX(version) AS MaxVersion
FROM
	nmruserdata.dbo.samples s,
	nmruserdata.dbo.relation_samples_to_users rsu
WHERE
	rsu.username='##username##' AND rsu.sampleid=s.id AND s.name='##name##';
".Replace("##username##", SessionFacade.Username).Replace("##name##", SpecName);
		//getLastVersionSELECTQuery.CommandText = Constants.SelectLastSampleVersion.Replace("##username##", SessionFacade.Username).Replace("##name##", SpecName);
		SqlDataReader myReader = getLastVersionSELECTQuery.ExecuteReader();
		if (!myReader.HasRows) result = 0;
		else
		{
			result = 0;
			while (myReader.Read()) result = uint.Parse(myReader["MaxVersion"].ToString());
		}

		MyConnection.Close();
		return result;
	}

	protected void Page_Load(object sender, EventArgs e)
	{
		#region Verify Authentication
		// Re-verify authentication
		PageInfo pageInfo = Pages.Instance.FindPage(Request["Page"]);
		if (pageInfo == null)
		{
			// Page creation
			if (SessionFacade.LoginKey == null && !Settings.PublicAccess)
			{
				Response.Redirect("Login.aspx?Redirect=MySpectrometers.aspx?Page=" + Request["Page"]);
			}
			if (!Settings.UsersCanCreateNewPages && !SessionFacade.Admin)
			{
				Response.Redirect("AccessDenied.aspx");
			}
		}
		else
		{
			// Page modification
			if (pageInfo.Provider.ReadOnly) Response.Redirect(pageInfo.Name + Settings.PageExtension);

			if (pageInfo.Status == PageStatus.Locked)
			{
				if (SessionFacade.LoginKey == null || (SessionFacade.LoginKey != null && (SessionFacade.LoginKey == null || !SessionFacade.Admin)))
				{
					Response.Redirect("AccessDenied.aspx");
				}
			}

			// Login/Authorization check
			if (SessionFacade.LoginKey == null && !Settings.PublicAccess && pageInfo.Status != PageStatus.Public)
			{
				Response.Redirect("Login.aspx?Redirect=" + Tools.UrlEncode(Request.Url.ToString().Substring(Request.Url.ToString().ToLowerInvariant().IndexOf("MySpectrometers.aspx"))));
			}
			if ((Settings.PublicAccess || pageInfo.Status == PageStatus.Public) && SessionFacade.LoginKey == null)
			{
				//lblIpLogged.Visible = true;
			}
		}
		#endregion

		SamplesDataSource.SelectParameters["username"].DefaultValue = SessionFacade.Username;
		PrevVersionsSQLDatasource.SelectParameters["username"].DefaultValue = SessionFacade.Username;

		if (Request["SampleID"] != null)
		{
			SampleTools.ActiveViewIndex = 2;
			showSampleInfowithID(Request["SampleID"]);
		}
	}
}
